import re
import datetime

import sqlparse
from app.core.config import exec_log
from sqlparse.tokens import Keyword
# from sqlparse.tokens import Whitespace

from .extra_tables import extract_tables
from .sql_execute import mysql_execute

from app.core.config import TMP_DATABASE_INSTANCE_HOST
from app.core.config import TMP_DATABASE_INSTANCE_PORT
from app.core.config import TMP_DATABASE_INSTANCE_USER
from app.core.config import TMP_DATABASE_INSTANCE_PASSWORD
from app.core.config import VARCHAR_MAX_LENGTH
from app.core.config import is_clean_tmp_data

from .get_audit_config import get_tables_columns_max_value


def create_table_filter(sql, table_name, database_tmp_name):
    # exec_log.info(sql)
    exec_log.info(f"Now creating the tmp table {table_name}.")
    sql_execute_in_tmp_database(sql, database_tmp_name)

    columns_detail = get_columns_details(table_name=table_name, database_tmp_name=database_tmp_name)
    exec_log.debug(columns_detail)
    column_info_details_check_result = table_info_check(columns_detail)

    return column_info_details_check_result


def get_create_table_sql(table_name, mysql_connect_dict):
    show_create_sql = f"show create table {table_name};"
    create_table_sql = mysql_execute(execute_sql=show_create_sql, **mysql_connect_dict)
    return create_table_sql[0].get('Create Table')


def alter_table_filter(sql, operation, table_name, database_name, database_tmp_name, mysql_connect_dict):
    create_table_sql = get_create_table_sql(table_name=table_name, mysql_connect_dict=mysql_connect_dict)
    sql_execute_in_tmp_database(database_tmp_name=database_tmp_name, sql=create_table_sql)
    table_columns_before = get_columns_details(table_name, database_tmp_name)

    sql_execute_in_tmp_database(database_tmp_name=database_tmp_name, sql=sql)
    table_columns_after = get_columns_details(table_name, database_tmp_name)
    columns_add, columns_modify, columns_drop, column_modify_detail = judge_columns_detail(table_columns_before,
                                                                                           table_columns_after)

    warning_msg = []
    suggestion_msg = []
    for column in columns_add:
        column_judge_result, judge_msg = column_judge(column)
        if column_judge_result == 0:
            # return column_judge_result, judge_msg
            return {
                'table_name': table_name,
                'success': 0,
                'meg': judge_msg,
                'level': 2,
                'level_msg': 'error'
            }
        elif column_judge_result == 2:
            warning_msg.append(judge_msg)
        elif column_judge_result == 3:
            suggestion_msg.append(judge_msg)
    # return create_table_sql
    for column in columns_modify:
        column_judge_result, judge_msg = column_judge(column)
        if column_judge_result == 0:
            # return column_judge_result, judge_msg
            return {
                'table_name': table_name,
                'success': 0,
                'meg': judge_msg,
                'level': 2,
                'level_msg': 'error'
            }
        elif column_judge_result == 2:
            warning_msg.append(judge_msg)
        elif column_judge_result == 3:
            suggestion_msg.append(judge_msg)
    if len(warning_msg) != 0:
        if len(suggestion_msg) == 0:
            return_msg = '亚美科技DBA提醒你: warning: ' + ';'.join(warning_msg) + '.  suggestion: ' + ';'.join(suggestion_msg)
        else:
            return_msg = '亚美科技DBA提醒你: suggestion: ' + ';'.join(suggestion_msg)
        return {
            'table_name': table_name,
            'success': 1,
            'meg': return_msg,
            'level': 1,
            'level_msg': 'warning'
        }

    if len(suggestion_msg) != 0:
        return {
            'table_name': table_name,
            'success': 1,
            'meg': '亚美科技DBA提醒你: suggestion: ' + ';'.join(suggestion_msg),
            'level': 3,
            'level_msg': 'suggestion'
        }
    if len(columns_drop) != 0 and operation == 2:
        table_backup(database_name, table_name, **mysql_connect_dict)
    return {
        'table_name': table_name,
        'success': 1,
        'meg': f'检查通过',
        'level': 0,
        'level_msg': 'info'
    }


def table_backup(database_name, table_name, **kwargs):
    pass


def tmp_data_clean(database_tmp_name):
    drop_database_sql = f'drop database {database_tmp_name} ;'
    mysql_execute(execute_sql=drop_database_sql,
                  mysql_con_host=TMP_DATABASE_INSTANCE_HOST,
                  mysql_con_port=TMP_DATABASE_INSTANCE_PORT,
                  mysql_con_user=TMP_DATABASE_INSTANCE_USER,
                  mysql_con_psw=TMP_DATABASE_INSTANCE_PASSWORD,
                  )


def get_columns_details(table_name, database_tmp_name):
    get_columns_sql = f"""select * from information_schema.columns 
    where TABLE_SCHEMA = '{database_tmp_name}' and TABLE_NAME = '{table_name}' ;"""
    columns_detail = mysql_execute(execute_sql=get_columns_sql,
                                   mysql_con_host=TMP_DATABASE_INSTANCE_HOST,
                                   mysql_con_port=TMP_DATABASE_INSTANCE_PORT,
                                   mysql_con_user=TMP_DATABASE_INSTANCE_USER,
                                   mysql_con_psw=TMP_DATABASE_INSTANCE_PASSWORD,
                                   database=database_tmp_name,
                                   )
    return columns_detail


def get_columns_name(table_name, **kwargs):
    # {'mysql_con_host': '10.200.11.26', 'mysql_con_port': 4001, 'mysql_con_user': 'a_dba_rw',
    #  'mysql_con_psw': 'X5KL!qm_WuD4wJ0T', 'database': 'flowla'}
    mysql_con_host = kwargs.get('mysql_con_host')
    mysql_con_port = kwargs.get('mysql_con_port')
    mysql_con_user = kwargs.get('mysql_con_user')
    mysql_con_psw = kwargs.get('mysql_con_psw')
    database = kwargs.get('database')
    get_columns_sql = f"""select COLUMN_NAME from information_schema.columns 
    where TABLE_SCHEMA = '{database}' and TABLE_NAME = '{table_name}' ;"""
    columns_detail = mysql_execute(execute_sql=get_columns_sql,
                                   mysql_con_host=mysql_con_host,
                                   mysql_con_port=mysql_con_port,
                                   mysql_con_user=mysql_con_user,
                                   mysql_con_psw=mysql_con_psw,
                                   database=database,
                                   )
    return columns_detail


def table_info_check(columns_detail):
    max_columns_value, columns_warning_value = get_tables_columns_max_value()
    if not isinstance(columns_detail, list):
        # assert False, 'Get column error! '
        raise RuntimeError('Get column error! ')

    table_name = columns_detail[0].get('TABLE_NAME')
    check_result_info = {'table_name': table_name}
    if len(columns_detail) >= max_columns_value:
        check_result_info.update({
            'success': 0,
            'meg': f'错误:数据列数据超出限制,表最多能有数据列:{max_columns_value},请联系DBA协商',
            'level': 2,
            'level_msg': 'error'
        })
        return check_result_info
    elif columns_warning_value < len(columns_detail) < max_columns_value:
        check_result_info.update({
            'success': 0,
            'meg': f'警告:数据列数据超出限制,列数量最好不超过:{columns_warning_value}，请三思. ',
            'level': 1,
            'level_msg': 'warning'
        })
        return check_result_info
    columns_check_result, columns_check_msg = columns_judge(columns_detail)
    if columns_check_result == 0:
        check_result_info.update({
            'success': 0,
            'meg': columns_check_msg,
            'level': 2,
            'level_msg': 'error'
        })
        return check_result_info
    if columns_check_result == 2:
        check_result_info.update({
            'success': 1,
            'meg': columns_check_msg,
            'level': 1,
            'level_msg': 'warning'
        })
        return check_result_info
    if columns_check_result == 3:
        check_result_info.update({
            'success': 1,
            'meg': columns_check_msg,
            'level': 3,
            'level_msg': 'suggestion'
        })
        return check_result_info
    check_result_info.update({
        'success': 1,
        'meg': f'检查通过',
        'level': 0,
        'level_msg': 'info'
    })
    return check_result_info


def column_judge(column):
    """

    :param column:
    :return: 0 error , 1: OK , 2: warning , 3: suggestion
    """
    column_name = column.get('COLUMN_NAME')
    date_type = column.get('DATA_TYPE')
    varchar_max_length = column.get('CHARACTER_MAXIMUM_LENGTH')
    column_key = column.get('COLUMN_KEY')
    is_nullable = column.get('IS_NULLABLE')
    if date_type == 'blob':
        return 0, f'列{column_name}数据类型有误,不能有数据类型为:{date_type}的列.'
    if date_type == 'text':
        return 2, f'列{column_name},不建议使用数据类型:{date_type}.'
    if varchar_max_length is not None and varchar_max_length > VARCHAR_MAX_LENGTH:
        return 0, f'varchar字段{column_name}不能超过{VARCHAR_MAX_LENGTH}'
    if not bool(re.match("^[a-z0-9_]*$", column_name)):
        return 0, f'列({column_name})命名不符合规范，列名只能由小写字母，数字，下划线组成.'

    if (column_name.endswith('type') or column_name.endswith('status') or column_name.endswith(
            'stats') or column_name.endswith('stat')) and date_type != 'tinyint':
        return 3, f'列{column_name}是否为状态列?是否考虑使用tinyint?.'
    if is_nullable == 'YES':
        return 3, f'列{column_name}建议为 NOT NULL'
    if column_name.endswith('id') and column_key == '':
        return 3, f'列{column_name}后缀为id，是否需要考虑为其添加索引?'
    return 1, '字段OK'


def columns_judge(columns_detail):
    column_must_contain = {'create_time': 'int', 'update_time': 'int'}
    key_found = set()
    other_key_found_flag = 0
    warning_msg = []
    suggestion_msg = []
    for column in columns_detail:
        column_name = column.get('COLUMN_NAME')
        date_type = column.get('DATA_TYPE')
        column_key = column.get('COLUMN_KEY')
        if column_key != 'PRI' and column_key != "":
            other_key_found_flag = 1
        column_judge_result, judge_msg = column_judge(column)
        if column_judge_result == 0:
            return column_judge_result, judge_msg
        elif column_judge_result == 2:
            warning_msg.append(judge_msg)
        elif column_judge_result == 3:
            suggestion_msg.append(judge_msg)
        if column_must_contain.get(column_name) is not None:
            key_found.add(column_name)
            if column_must_contain.get(column_name) != date_type:
                return 0, f'列不符合规范{column_name},数据类型不匹配'
    for key, values in column_must_contain.items():
        if key not in key_found:
            return 0, f'没有包含列{key}'
    if other_key_found_flag == 0:
        warning_msg.append('除主键外,没有看到有其它索引，请确认')
    if len(warning_msg) != 0:
        if len(suggestion_msg) != 0:
            return 2, '亚美科技DBA提醒你: warning: ' + ';'.join(warning_msg) + '.  suggestion: ' + ';'.join(suggestion_msg)
        return 2, ';'.join(warning_msg)
    if len(suggestion_msg) != 0:
        return 3, '亚美科技DBA提醒你: suggestion: ' + ';'.join(suggestion_msg)
    return 1, f'列检查通过'


def judge_columns_detail(table_columns_before, table_columns_after):
    column_dict_before = list_trans_to_dict(table_columns_before)
    column_dict_after = list_trans_to_dict(table_columns_after)
    return judge_columns_the_same(column_dict_before, column_dict_after)


def judge_dict_the_same(dict1, dict2):
    key_diff = []
    for key in dict1.keys():
        if dict1.get(key) != dict2.get(key):
            key_diff.append(dict2.get(key))
    if len(key_diff) == 0:
        return True, None
    else:
        return False, key_diff


def judge_columns_the_same(table_columns_before, table_columns_after):
    # shared_items = [dict1[k] for k in dict1 if k in dict2 and dict1[k] != dict2[k]]
    # print(shared_items)
    columns_add = []
    columns_modify = []
    columns_drop = []
    column_modify_detail = []
    for column_before_key in table_columns_before.keys():
        after_column = table_columns_after.get(column_before_key)
        if after_column is None:
            columns_drop.append(table_columns_before.get(after_column))
        else:
            # if table_columns_before.get(column_before_key) == table_columns_after.get(column_before_key):
            judge_result, column_type_diff = judge_dict_the_same(table_columns_before.get(column_before_key),
                                                                 table_columns_after.get(column_before_key))
            if not judge_result:
                columns_modify.append(table_columns_after.get(column_before_key))
                column_modify_detail.append(column_type_diff)
    for column_after_key in table_columns_after:
        before_column = table_columns_before.get(column_after_key)
        if before_column is None:
            columns_add.append(table_columns_after.get(column_after_key))
    return columns_add, columns_modify, columns_drop, column_modify_detail


def list_trans_to_dict(columns_list):
    column_dict_return = {}
    for column_detail in columns_list:
        column_name = column_detail.get('COLUMN_NAME')
        column_dict_return.update({column_name: column_detail})
    return column_dict_return


def create_tmp_database(database_tmp_name):
    create_database_sql = f'create database if not exists  {database_tmp_name} ;'
    exec_log.debug(create_database_sql)
    mysql_execute(execute_sql=create_database_sql,
                  mysql_con_host=TMP_DATABASE_INSTANCE_HOST,
                  mysql_con_port=TMP_DATABASE_INSTANCE_PORT,
                  mysql_con_user=TMP_DATABASE_INSTANCE_USER,
                  mysql_con_psw=TMP_DATABASE_INSTANCE_PASSWORD,
                  )


def sql_execute_in_tmp_database(sql, database_tmp_name):
    mysql_execute(execute_sql=sql,
                  mysql_con_host=TMP_DATABASE_INSTANCE_HOST,
                  mysql_con_port=TMP_DATABASE_INSTANCE_PORT,
                  mysql_con_user=TMP_DATABASE_INSTANCE_USER,
                  mysql_con_psw=TMP_DATABASE_INSTANCE_PASSWORD,
                  database=database_tmp_name)


def get_ddl_table_name(sql):
    table_reference = extract_tables(sql)[0]
    table_name, db_name = table_reference.name, table_reference.schema
    if table_name is not None:
        table_name = table_name.replace('`', '')
    if db_name is not None:
        db_name = db_name.replace('`', '')
    return table_name, db_name


def get_next_keyword_token(sql_tokens, i):
    while i < len(sql_tokens):
        if sql_tokens[i].is_keyword is True:
            return sql_tokens[i], i
        i += 1


def analyze_one_sql(sql, operation=1, database_tmp_name=None, database_name=None, mysql_connect_dict={}):
    sql_type = get_sql_type(sql)
    if sql_type == 'CREATE':
        sql_a = sqlparse.parse(sql)[0]
        create_key_token, create_key_token_index = get_next_keyword_token(sql_a.tokens, 0)
        table_key_token, table_key_token_index = get_next_keyword_token(sql_a.tokens, create_key_token_index + 1)
        if table_key_token.ttype is Keyword and table_key_token.value.upper() == "TABLE":
            table_name, db_name_in_sql = get_ddl_table_name(sql)
            if db_name_in_sql is not None:
                return {
                    'success': 0,
                    'meg': '语句不能带库名',
                    'level': 2,
                    'level_msg': 'error'
                }
            check_result = create_table_filter(sql, table_name, database_tmp_name)
            return check_result

    if sql_type == 'ALTER':
        sql_a = sqlparse.parse(sql)[0]
        alter_key_token, alter_key_token_index = get_next_keyword_token(sql_a.tokens, 0)
        table_key_token, table_key_token_index = get_next_keyword_token(sql_a.tokens, alter_key_token_index + 1)
        if table_key_token.ttype is Keyword and table_key_token.value.upper() == "TABLE":
            table_name, db_name_in_sql = get_ddl_table_name(sql)
            if db_name_in_sql is not None:
                return {
                    'success': 0,
                    'meg': '语句不能带库名',
                    'level': 2,
                    'level_msg': 'error'
                }
            check_result = alter_table_filter(sql, operation, table_name, database_name, database_tmp_name,
                                              mysql_connect_dict)
            return check_result


def analyze_one_sql_not_used(sql, database_name=None):
    """没用了"""
    sql_type = get_sql_type(sql)
    if sql_type == 'CREATE':
        sql_a = sqlparse.parse(sql)[0]
        for i in range(len(sql_a.tokens)):
            sql_token = sql_a.tokens[i]
            # for sql_token in sql_a.tokens:
            # 判断是否为crete table 语句
            # key_token, ii = get_next_not_whitespace_token(sql_a.tokens, 0)
            if sql_token.ttype is Keyword and sql_token.value.upper() == "TABLE":
                table_name, db_name_in_sql = get_ddl_table_name(sql)
                if db_name_in_sql is not None:
                    return False
                check_result = create_table_filter(sql, table_name, database_name)
                return check_result

    if sql_type == 'ALTER':
        pass


def sql_analyze_filter(sql_to_execute, operation=1, database_name=None, mysql_connect_dict={}):
    sql_to_execute_list = sqlparse.split(sql_to_execute)
    check_result_list = []

    now_time = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
    database_tmp_name = f'{database_name}{now_time}'
    exec_log.info(f"Now creating the tmp database {database_tmp_name}.")
    create_tmp_database(database_tmp_name=database_tmp_name)

    for sql in sql_to_execute_list:
        check_result = analyze_one_sql(sql=sql, operation=operation, database_tmp_name=database_tmp_name,
                                       mysql_connect_dict=mysql_connect_dict, database_name=database_name)
        if check_result is not None:
            check_result_list.append(check_result)
    if is_clean_tmp_data == 1:
        exec_log.info(f"Now dropping the tmp database {database_tmp_name}.")
        tmp_data_clean(database_tmp_name)
    return check_result_list


def get_sql_type(sql):
    '''
    返回sql的类型： strings
    如：'INSERT','DELETE','ALTER','DROP','CREATE','SELECT','UPDATE','UNKNOWN'
    '''
    if not isinstance(sql, str):
        raise ValueError('@sql should be a str type')
    parsed = sqlparse.parse(sql)
    sql_type = parsed[0].get_type()

    m_show, m_set, m_truncate = None, None, None
    if sql_type == 'UNKNOWN':
        # 找出UNKNOWN中的set,show命令，并将其的类别由UNKNOWN转为SHOW或SET
        m_show, m_set = re.match(
            r'^\s*show', sql, re.IGNORECASE), re.match(r'^\s*set', sql, re.IGNORECASE)
        # 找出UNKNOWN中的truncate命令，并将其的类别由UNKNOWN转为TRUNCATE
        m_truncate = re.match(r'^\s*truncate', sql, re.IGNORECASE)
    if m_show is not None:
        sql_type = 'SHOW'
    elif m_set is not None:
        sql_type = 'SET'
    elif m_truncate is not None:
        sql_type = 'TRUNCATE'
    return sql_type
